﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class RoyalItemControlDAL : IRoyalItemControlDAL
    {

        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public RoyalItemControlDAL()
        {

        }

        public IList<RoyalItemControlInfo> ListRoyalItemControls(RoyalItemControlInfo ctrlInfo)
        {
            string strSQL;

            List<RoyalItemControlInfo> InfoList = new List<RoyalItemControlInfo>();

            try 
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@Sequence", ctrlInfo.Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT r.CTRL_SEQ, r.EMP_SEQ, r.ROYAL_ITEM_SEQ, r.GET_DATE, r.RETURN_DATE"
                        + ", r.RETURN_FLAG, r.AMOUNT, r.REMARKS, r.RECORD_STATUS, r.CREATED_BY, r.UPDATED_BY"
                   + " FROM HRM_PS_ROYAL_ITEM_RETURNS r"
                   + " WHERE r.RECORD_STATUS not like @DeleteRecord"
                   + (ctrlInfo.Sequence > 0 ? " AND r.RETURN_SEQ = @Sequence" : "");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(1)));
                        empInfo = empList[0];
                    }
                    RoyalItemDAL riDAL = new RoyalItemDAL();
                    RoyalItemInfo riInfo = new RoyalItemInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<RoyalItemInfo> riList = riDAL.ListRoyalItems(new RoyalItemInfo(rdr.GetInt32(2)));
                    }

                    RoyalItemControlInfo Info = new RoyalItemControlInfo(rdr.GetInt32(0)
                        , empInfo, riInfo
                        , rdr.IsDBNull(3) ? (DateTime?)null : rdr.GetDateTime(3)
                        , rdr.IsDBNull(4) ? (DateTime?)null : rdr.GetDateTime(4)
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , rdr.IsDBNull(6) ? 0 : rdr.GetDouble(6)
                        , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                        , rdr.IsDBNull(9) ? (int?)null : rdr.GetInt32(9)
                        , rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10)
                        );
                    InfoList.Add(Info);                       
                }

                return InfoList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public IList<RoyalItemControlInfo> GridRoyalItemControls(RoyalItemControlInfo ctrlInfo)
        {
            string strSQL;

            List<RoyalItemControlInfo> InfoList = new List<RoyalItemControlInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@Sequence", ctrlInfo.Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT EMP_SEQ"
                    + ", CASE WHEN SUM(ITEM_34) > 0 THEN SUM(ITEM_34) ELSE '' END AS ITEM_34"
                    + ", CASE WHEN SUM(ITEM_33) > 0 THEN SUM(ITEM_33) ELSE '' END AS ITEM_33"
                    + ", CASE WHEN SUM(ITEM_29) > 0 THEN SUM(ITEM_29) ELSE '' END AS ITEM_29"
                    + ", CASE WHEN SUM(ITEM_28) > 0 THEN SUM(ITEM_28) ELSE '' END AS ITEM_28"
                    + ", CASE WHEN SUM(ITEM_24) > 0 THEN SUM(ITEM_24) ELSE '' END AS ITEM_24"
                    + ", CASE WHEN SUM(ITEM_23) > 0 THEN SUM(ITEM_23) ELSE '' END AS ITEM_23"
                    + ", CASE WHEN SUM(ITEM_16) > 0 THEN SUM(ITEM_16) ELSE '' END AS ITEM_16"
                    + ", CASE WHEN SUM(ITEM_15) > 0 THEN SUM(ITEM_15) ELSE '' END AS ITEM_15"
                    + ", CASE WHEN SUM(ITEM_11) > 0 THEN SUM(ITEM_11) ELSE '' END AS ITEM_11"
                    + ", CASE WHEN SUM(ITEM_10) > 0 THEN SUM(ITEM_10) ELSE '' END AS ITEM_10"
                    + ", CASE WHEN SUM(ITEM_09) > 0 THEN SUM(ITEM_09) ELSE '' END AS ITEM_09"
                    + ", CASE WHEN SUM(ITEM_08) > 0 THEN SUM(ITEM_08) ELSE '' END AS ITEM_08"
                    + " FROM("
                        + " SELECT h.EMP_SEQ"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 34 THEN YEAR(h.RECEIVED_DATE) + 543  ELSE 0 END AS ITEM_34"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 33 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_33"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 29 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_29"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 28 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_28"

                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 24 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_24"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 23 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_23"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 16 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_16"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 15 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_15"

                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 11 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_11"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 10 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_10"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 9 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_09"
                        + ", CASE WHEN h.ROYAL_ITEM_SEQ = 8 THEN YEAR(h.RECEIVED_DATE) + 543 ELSE 0 END AS ITEM_08"

                        + " FROM HRM_PS_ROYAL_ITEM_HISTORIES h"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON h.EMP_SEQ = e.EMP_SEQ"// ชั่วคราว
                        + " WHERE h.RECORD_STATUS not like @DeleteRecord"
                        + " AND e.POS_SEQ IS NOT NULL"// ชั่วคราว
                    + ") t"
                    + " GROUP BY EMP_SEQ";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(0))
                    {
                        IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(0)));
                        empInfo = empList[0];
                    }

                    RoyalItemControlInfo Info = new RoyalItemControlInfo();

                    Info.Employee = empInfo;
                    Info.Item34 = rdr.IsDBNull(1) ? "" : rdr.GetInt32(1).ToString();
                    Info.Item33 = rdr.IsDBNull(2) ? "" : rdr.GetInt32(2).ToString();
                    Info.Item29 = rdr.IsDBNull(3) ? "" : rdr.GetInt32(3).ToString();
                    Info.Item28 = rdr.IsDBNull(4) ? "" : rdr.GetInt32(4).ToString();
                    Info.Item24 = rdr.IsDBNull(5) ? "" : rdr.GetInt32(5).ToString();
                    Info.Item23 = rdr.IsDBNull(6) ? "" : rdr.GetInt32(6).ToString();
                    Info.Item16 = rdr.IsDBNull(7) ? "" : rdr.GetInt32(7).ToString();
                    Info.Item15 = rdr.IsDBNull(8) ? "" : rdr.GetInt32(8).ToString();
                    Info.Item11 = rdr.IsDBNull(9) ? "" : rdr.GetInt32(9).ToString();
                    Info.Item10 = rdr.IsDBNull(10) ? "" : rdr.GetInt32(10).ToString();
                    Info.Item09 = rdr.IsDBNull(11) ? "" : rdr.GetInt32(11).ToString();
                    Info.Item08 = rdr.IsDBNull(12) ? "" : rdr.GetInt32(12).ToString();

                    InfoList.Add(Info);
                }

                return InfoList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void Insert(RoyalItemControlInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(1, "@RoyalItem", Info.RoyalItem == null ? (int?)null : Info.RoyalItem.Sequence);
                dbManager.AddParameters(2, "@GetDate", Info.GetDate);
                dbManager.AddParameters(3, "@Remarks", Info.Remarks);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_ROYAL_ITEM_CONTROLS(EMP_SEQ, ROYAL_ITEM_SEQ, GET_DATE"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES(@EmpSeq, @RoyalItem, @GetDate, @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";
 
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update(RoyalItemControlInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@EmpSeq", Info.Employee == null ? (int?)null : Info.Employee.Sequence);
                dbManager.AddParameters(2, "@RoyalItem", Info.RoyalItem == null ? (int?)null : Info.RoyalItem.Sequence);
                dbManager.AddParameters(3, "@GetDate", Info.GetDate);
                dbManager.AddParameters(4, "@Remarks", Info.Remarks);
                dbManager.AddParameters(5, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(6, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ROYAL_ITEM_CONTROLS"
                    + " SET EMP_SEQ = @EmpSeq"
                        + ", ROYAL_ITEM_SEQ = @RoyalItem"
                        + ", GET_DATE = @GetDate"
                        + ", REMARKS = @Remarks"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CTRL_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateReturn(RoyalItemControlInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@ReturnDate", Info.ReturnDate);
                dbManager.AddParameters(2, "@ReturnFlag", Info.ReturnFlag);
                dbManager.AddParameters(3, "@ReturnAmount", Info.ReturnAmount);
                dbManager.AddParameters(4, "@Remarks", Info.Remarks);
                dbManager.AddParameters(5, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(6, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ROYAL_ITEM_CONTROLS"
                    + " SET RETURN_DATE = @ReturnDate"
                        + ", RETURN_FLAG = @ReturnFlag"
                        + ", RETURN_AMOUNT = @ReturnAmount"
                        + ", REMARKS = @Remarks"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CTRL_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_ROYAL_ITEM_CONTROLS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CTRL_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }    
    }
}
